package com.anji.plus.gaea.export.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.anji.plus.gaea.code.ResponseCode;
import com.anji.plus.gaea.exception.BusinessExceptionBuilder;
import com.anji.plus.gaea.export.constant.ExpConstant;
import com.anji.plus.gaea.export.vo.ExportOperation;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * TODO
 *
 * @author chenkening
 * @date 2021/3/26 10:30
 */
public class GaeaReportExcelUtil {

    /**
     * 记录日志
     */
    protected static Logger logger = LoggerFactory.getLogger(GaeaReportExcelUtil.class);

    public static void excelExportByFilePath(ExportOperation exportOperation) {
        JSONObject jsonObject = JSONObject.parseObject(exportOperation.getJsonStr());
        JSONObject rows = jsonObject.getJSONObject("rows");
        JSONObject cols = jsonObject.getJSONObject("cols");
        if (cols.containsKey("len")) {
            cols.remove("len");
        }

        if (rows.containsKey("-1")) {
            rows.remove("-1");
        }
        JSONArray photos = jsonObject.getJSONArray("QRCodeOrBarCode");
        // 所有的样式数组。
        JSONArray styles = jsonObject.getJSONArray("styles");

//        1.创建一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();


//        2.创建一个工作表
        XSSFSheet sheet = workbook.createSheet("news");
        FileOutputStream fileOutputStream = null;
        try {
            sheet.autoSizeColumn(1, true);
            // 设置默认列宽
            sheet.setDefaultColumnWidth(12);
            sheet.setDefaultRowHeight((short) 360);
            Iterator<String> colsIte = cols.keySet().iterator();
            while (colsIte.hasNext()) {
                String next = colsIte.next();   //列
                JSONObject colStyle = cols.getJSONObject(next);
                if (colStyle.containsKey("width")) {
                    // 自定义 width * 32
                    sheet.setColumnWidth(Integer.parseInt(next), colStyle.getInteger("width") * 30);
                }
            }

            if (photos != null) {
                // make photo
                makePhoto(photos, workbook, sheet);
            }
            for (Map.Entry<String, Object> entry : rows.entrySet()) {
                if (!entry.getKey().contains("len")) {

                    // 创建一行
                    Row row = sheet.createRow(Integer.parseInt(entry.getKey()));
                    JSONObject jsonObject1 = (JSONObject) entry.getValue();

                    //设置行高
                    if (jsonObject1.containsKey("height")) {
                        int height = (int) jsonObject1.get("height");
                        row.setHeight((short) (height * 14.7));
                    }
                    // cells 获取到一行
                    JSONObject cells = jsonObject1.getJSONObject("cells");
                    if (cells.containsKey("-1")) {
                        cells.remove("-1");
                    }

                    setCellStyle(cells, workbook, sheet ,  styles, row);

                }
            }

            Iterator<String> iterator = rows.keySet().iterator();
            while (iterator.hasNext()) {
                String rownum = iterator.next();    //行
                if (rownum.equals("len")) {
                    continue;
                }
                JSONObject cells = rows.getJSONObject(rownum).getJSONObject("cells");
                if (cells == null) {
                    continue;
                } else {
                    Iterator<String> cellsIte = cells.keySet().iterator();
                    while (cellsIte.hasNext()) {
                        String cellnum = cellsIte.next();    //列
                        JSONObject cellJSON = cells.getJSONObject(cellnum);
                        if (cellJSON != null) {
                            if (cellJSON.containsKey("merge")) {
                                List<Integer> list = (List<Integer>) cellJSON.get("merge");
//                        // 起始行, 终止行, 起始列, 终止列
                                CellRangeAddress cra = new CellRangeAddress(Integer.parseInt(rownum), Integer.parseInt(rownum) + list.get(0), Integer.parseInt(cellnum), Integer.parseInt(cellnum) + list.get(1));
                                sheet.addMergedRegion(cra);


                                if (cellJSON.containsKey("style")) {
                                    int style = (int) cellJSON.get("style");
                                    JSONObject jsonObject4 = styles.getJSONObject(style);
                                    if (jsonObject4.containsKey("border")) {
                                        JSONObject borders = jsonObject4.getJSONObject("border");

                                        Iterator<String> borderIterator = borders.keySet().iterator();
                                        while (borderIterator.hasNext()) {
                                            String borderSide = borderIterator.next();
                                            // 设置单元格的边框
                                            switch (borderSide) {
                                                case "top":
                                                    JSONArray top = borders.getJSONArray("top");
                                                    // 上边框
                                                    RegionUtil.setBorderTop(BorderStyle.valueOf(top.get(0).toString().toUpperCase()).getCode(), cra, sheet);
                                                case "left":
                                                    JSONArray left = borders.getJSONArray("left");
                                                    // 左边框
                                                    RegionUtil.setBorderLeft(BorderStyle.valueOf(left.get(0).toString().toUpperCase()).getCode(), cra, sheet);
                                                case "bottom":
                                                    JSONArray bottom = borders.getJSONArray("bottom");
                                                    // 下边框
                                                    RegionUtil.setBorderBottom(BorderStyle.valueOf(bottom.get(0).toString().toUpperCase()).getCode(), cra, sheet);
                                                case "right":
                                                    JSONArray right = borders.getJSONArray("right");
                                                    // 右边框
                                                    RegionUtil.setBorderRight(BorderStyle.valueOf(right.get(0).toString().toUpperCase()).getCode(), cra, sheet);
                                                default:
                                                    break;
                                            }
                                        }
                                    }
                                }

                            }
                        }

                    }
                }
            }
            fileOutputStream = new FileOutputStream(exportOperation.getFilePath());
            workbook.write(fileOutputStream);
        } catch (Exception e) {
            logger.info("export excel error", e);
            throw BusinessExceptionBuilder.build(ResponseCode.FAIL_CODE, e.getMessage());
        }finally {
            try {
                fileOutputStream.close();
                workbook.close();
            } catch (IOException e) {
                throw BusinessExceptionBuilder.build(ResponseCode.FAIL_CODE, e.getMessage());
            }
        }

    }


    /**
     *
     * @param cells 当前行每一列
     * @param workbook
     * @param styles    样式
     * @param row   当前行
     */
    private static void setCellStyle(JSONObject cells, XSSFWorkbook workbook, XSSFSheet sheet ,JSONArray styles, Row row) {

        for (Map.Entry<String, Object> entry2 : cells.entrySet()) {

            XSSFCellStyle cellStyle = workbook.createCellStyle();
            DataFormat df = workbook.createDataFormat();

            JSONObject jsonObject3 = (JSONObject) entry2.getValue();
            // 创建一列
            Cell cell = row.createCell(Integer.parseInt(entry2.getKey()));
            Object text = jsonObject3.get("text");
            String cellValue = null;
            if(text instanceof JSONObject){
                JSONObject jsonObject = (JSONObject) text;
                JSONArray jsonArray = new JSONArray();
                jsonArray.add(jsonObject);
                try {
                    makePhoto(jsonArray , workbook , sheet );
                } catch (Exception e) {
                    logger.info("makePhoto error" , e);
                }
            } else if(text instanceof String){
                cell = (Cell) text;
                // 创建第 某行 第几个单元格
                if (StringUtils.isNotBlank(cellValue)) {
                    cell.setCellValue(cellValue);
                    if (!CollectionUtils.isEmpty(ExpConstant.getExpFunction(cellValue))) {
                        cell.setCellFormula(cellValue.substring(1));
                        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
                        cell.setCellType(CellType.FORMULA);
                    }
                }
            }


            if (StringUtils.isNotBlank(jsonObject3.getString("style"))) {
                // 样式 id
                int style = (int) jsonObject3.get("style");
                JSONObject jsonObject4 = (JSONObject) styles.get(style);

                if (!jsonObject4.containsKey("valign")) {
                    cellStyle.setVerticalAlignment(VerticalAlignment.valueOf("CENTER"));
                } else {
                    String str1 = jsonObject4.getString("valign").toUpperCase();
                    if ("MIDDLE".equals(str1)) {
                        str1 = "CENTER";
                    }
                    cellStyle.setVerticalAlignment(VerticalAlignment.valueOf(str1));
                }
                // 设置局中
                if (jsonObject4.containsKey("align")) {
                    String align = (String) jsonObject4.get("align");
                    cellStyle.setAlignment(HorizontalAlignment.valueOf(align.toUpperCase()));

                }

                //------------------------bgColor
                if (jsonObject4.containsKey("bgcolor")) {

                    String str1 = jsonObject4.getString("bgcolor");
                    XSSFColor xSSFColor = new XSSFColor();
                    if (str1.indexOf("#") != -1) {
                        byte[] arrayOfByte = {(byte) Integer.parseInt(str1.substring(1, 3), 16), (byte) Integer.parseInt(str1.substring(3, 5), 16), (byte) Integer.parseInt(str1.substring(5, 7), 16)};
                        xSSFColor.setRGB(arrayOfByte);
                    }
                    cellStyle.setFillForegroundColor(xSSFColor);
                    cellStyle.setFillBackgroundColor(xSSFColor);
                    cellStyle.setFillPattern(FillPatternType.BIG_SPOTS);
                }


                if (jsonObject4.containsKey("format")) {
                    String format = (String) jsonObject4.get("format");
                    if(text instanceof String){
                        switch (format) {
                            case "formula":
                                // 函数
                                cell.setCellType(CellType.FORMULA);
                                break;
                            case "rmb":
                                //货币
                                if (StringUtils.isNotBlank(cellValue)) {
                                    try {

                                        cell.setCellValue(Integer.parseInt(cellValue));
                                        XSSFDataFormat xssfDataFormat = workbook.createDataFormat();
                                        cellStyle.setDataFormat(xssfDataFormat.getFormat("￥#,##0_);\\[Red](￥#,##0)"));
                                        cell.setCellStyle(cellStyle);
                                    } catch (Exception e) {
                                    }
                                }
                                break;
                            case "number":
                                // 数值
                                if (StringUtils.isNotBlank(cellValue)) {

                                    try {
                                        cell.setCellValue(Integer.parseInt(cellValue));
                                        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
                                        cell.setCellStyle(cellStyle);

                                    } catch (Exception e) {
                                    }
                                }
                                break;
                            case "date":

                                break;
                            case "date2":

                                break;
                            case "time":

                                break;
                            case "datetime":

                                break;
                            case "percent":
                                //百分比
                                if (StringUtils.isNotBlank(cellValue)) {

                                    try {
                                        cell.setCellValue(Integer.parseInt(cellValue) / 100);
                                        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
                                        cell.setCellStyle(cellStyle);

                                    } catch (Exception e) {
                                    }
                                }
                                break;
                            case "eur":
                                //欧元
                                if (StringUtils.isNotBlank(cellValue)) {
//
                                    try {

                                        cell.setCellValue(Integer.parseInt(cellValue));
                                        XSSFDataFormat xssfDataFormat = workbook.createDataFormat();
                                        cellStyle.setDataFormat(xssfDataFormat.getFormat("€#,##0_);\\[Red](￥#,##0)"));
                                        cell.setCellStyle(cellStyle);

                                    } catch (Exception e) {
                                    }
                                }
                                break;
                            case "usd":
                                //美元
                                if (StringUtils.isNotBlank(cellValue)) {
//
                                    try {
                                        cell.setCellValue(Integer.parseInt(cellValue));
                                        XSSFDataFormat xssfDataFormat = workbook.createDataFormat();
                                        cellStyle.setDataFormat(xssfDataFormat.getFormat("$#,##0_);\\[Red](￥#,##0)"));
                                        cell.setCellStyle(cellStyle);

                                    } catch (Exception e) {
                                    }
                                }
                                break;
                            default:
                                cell.setCellType(CellType.STRING);

                        }
                    }
                }
//                ------------------------------border--------------------------
                if (jsonObject4.containsKey("border")) {
                    JSONObject borders = (JSONObject) jsonObject4.get("border");

                    Iterator<String> borderIterator = borders.keySet().iterator();
                    while (borderIterator.hasNext()) {
                        String borderSide = borderIterator.next();
                        // 设置单元格的边框
                        switch (borderSide) {
                            case "top":
                                JSONArray top = borders.getJSONArray("top");
                                tracingBorder(BorderStyle.valueOf(top.get(0).toString().toUpperCase()).getCode(), "top", cellStyle);
                            case "left":
                                JSONArray left = borders.getJSONArray("left");
                                tracingBorder(BorderStyle.valueOf(left.get(0).toString().toUpperCase()).getCode(), "left", cellStyle);
                            case "bottom":
                                JSONArray bottom = borders.getJSONArray("bottom");
                                tracingBorder(BorderStyle.valueOf(bottom.get(0).toString().toUpperCase()).getCode(), "bottom", cellStyle);
                            case "right":
                                JSONArray right = borders.getJSONArray("right");
                                tracingBorder(BorderStyle.valueOf(right.get(0).toString().toUpperCase()).getCode(), "right", cellStyle);
                        }
                    }
                }

                if (jsonObject4.containsKey("font") || jsonObject4.containsKey("underline") || jsonObject4.containsKey("strike") || jsonObject4.containsKey("color")) {
                    XSSFFont xSSFFont = workbook.createFont();

                    //------------------------------font--------------------------
                    JSONObject fontObj = (JSONObject) jsonObject4.get("font");
                    Object object1 = jsonObject4.get("underline");
                    Object object2 = jsonObject4.get("strike");
                    if (null != fontObj) {
                        if (fontObj.containsKey("name")) {
                            xSSFFont.setFontName(fontObj.getString("name"));
                        }
                        if (fontObj.containsKey("italic")) {
                            xSSFFont.setItalic(true);
                        }
                        if (fontObj.containsKey("size")) {
                            xSSFFont.setFontHeightInPoints(fontObj.getShort("size").shortValue());
                        }
                        if (fontObj.containsKey("bold")) {
                            boolean bool = fontObj.getBoolean("bold").booleanValue();
                            xSSFFont.setBold(bool);
                        }
                    }

                    if (null != object1) {
                        xSSFFont.setUnderline((byte) 1);
                    }
                    if (null != object2) {
                        xSSFFont.setStrikeout(true);
                    }

                    //------------------------------color--------------------------
                    if (jsonObject4.containsKey("color")) {
                        String colorStr = jsonObject4.getString("color");
                        XSSFColor xssfColor = new XSSFColor();

                        if (colorStr.length() > 6) {
                            if (colorStr.contains("rgb")) {
                                colorStr = colorStr.replace("rgb", "").replace("(", "").replace(")", "");
                                String[] arrayOfString = colorStr.split(",");
                                if (arrayOfString.length == 3) {
                                    byte[] arrayOfByte = {(byte) Integer.parseInt(arrayOfString[0]), (byte) Integer.parseInt(arrayOfString[1]), (byte) Integer.parseInt(arrayOfString[2])};
                                    xssfColor.setRGB(arrayOfByte);
                                }
                            } else if (colorStr.contains("#")) {
                                byte[] arrayOfByte = {(byte) Integer.parseInt(colorStr.substring(1, 3), 16), (byte) Integer.parseInt(colorStr.substring(3, 5), 16), (byte) Integer.parseInt(colorStr.substring(5, 7), 16)};
                                xssfColor.setRGB(arrayOfByte);
                            }
                        }
                        xSSFFont.setColor(xssfColor);
                    }
                    cellStyle.setFont(xSSFFont);

                }
                cellStyle.setWrapText(true);
                cell.setCellStyle((cellStyle));

            }


        }
    }


    /**
     *
     * @param borderCode BordetStyle 枚举值
     * @param border     top、right、bottom 、left
     * @param cellStyle
     */
    public static void tracingBorder(int borderCode, String border, XSSFCellStyle cellStyle) {

        switch (borderCode) {
            case 0:
                switch (border) {
                    case "top":
                        cellStyle.setBorderTop(BorderStyle.NONE);
                        break;
                    case "left":
                        cellStyle.setBorderLeft(BorderStyle.NONE);
                        break;
                    case "bottom":
                        cellStyle.setBorderBottom(BorderStyle.NONE);
                        break;
                    case "right":
                        cellStyle.setBorderRight(BorderStyle.NONE);
                        break;
                    default:
                        break;
                }
                break;
            case 1:
                switch (border) {
                    case "top":
                        cellStyle.setBorderTop(BorderStyle.THIN);
                        break;
                    case "left":
                        cellStyle.setBorderLeft(BorderStyle.THIN);
                        break;
                    case "bottom":
                        cellStyle.setBorderBottom(BorderStyle.THIN);
                        break;
                    case "right":
                        cellStyle.setBorderRight(BorderStyle.THIN);
                        break;
                    default:
                        break;
                }
                break;
            case 2:
                switch (border) {
                    case "top":
                        cellStyle.setBorderTop(BorderStyle.MEDIUM);
                        break;
                    case "left":
                        cellStyle.setBorderLeft(BorderStyle.MEDIUM);
                        break;
                    case "bottom":
                        cellStyle.setBorderBottom(BorderStyle.MEDIUM);
                        break;
                    case "right":
                        cellStyle.setBorderRight(BorderStyle.MEDIUM);
                        break;
                    default:
                        break;
                }
                break;
            case 3:
                switch (border) {
                    case "top":
                        cellStyle.setBorderTop(BorderStyle.DASHED);
                        break;
                    case "left":
                        cellStyle.setBorderLeft(BorderStyle.DASHED);
                        break;
                    case "bottom":
                        cellStyle.setBorderBottom(BorderStyle.DASHED);
                        break;
                    case "right":
                        cellStyle.setBorderRight(BorderStyle.DASHED);
                        break;
                    default:
                        break;
                }
                break;
            case 4:
                cellStyle.setBorderBottom(BorderStyle.DOTTED);
                break;
            case 5:
                cellStyle.setBorderBottom(BorderStyle.THICK);
                break;
            case 6:
                cellStyle.setBorderBottom(BorderStyle.DOUBLE);
                break;
            case 7:
                cellStyle.setBorderBottom(BorderStyle.HAIR);
                break;
            case 8:
                cellStyle.setBorderBottom(BorderStyle.MEDIUM_DASHED);
                break;
            case 9:
                cellStyle.setBorderBottom(BorderStyle.DASH_DOT);
                break;
            case 10:
                cellStyle.setBorderBottom(BorderStyle.MEDIUM_DASH_DOT);
                break;
            case 11:
                cellStyle.setBorderBottom(BorderStyle.DASH_DOT_DOT);
                break;
            case 12:
                cellStyle.setBorderBottom(BorderStyle.MEDIUM_DASH_DOT_DOT);
                break;
            case 13:
                cellStyle.setBorderBottom(BorderStyle.SLANTED_DASH_DOT);
                break;
            default:
                break;
        }
    }

    /**
     * 导出excel生成图片
     * @param photos    图片数组
     * @param workbook
     * @param sheet
     * @throws Exception
     */
    public static void makePhoto(JSONArray photos, Workbook workbook, XSSFSheet sheet) throws Exception {
        ByteArrayOutputStream byteArrayOut = null;
        for (int i = 0; i < photos.size(); i++) {
            JSONObject qrCodeJson = photos.getJSONObject(i);

            if(qrCodeJson.get("type").toString().equals("QRCode")){
                // 二维码
                byteArrayOut = QrCodeOrBarCodeUtil.encodeQRCode
                        (qrCodeJson.getString("content"), "",  qrCodeJson.getInteger("width"), qrCodeJson.getInteger("height"));

            }else if(qrCodeJson.get("type").toString().equals("BarCode")){
                // 条形码
                byteArrayOut = QrCodeOrBarCodeUtil.encodeBarCode
                        (qrCodeJson.getString("content"),  qrCodeJson.getInteger("width"), qrCodeJson.getInteger("height"));

            }

            XSSFRow row = null;
            if(sheet.getRow(qrCodeJson.getInteger("ri")) == null){
                row = sheet.createRow(qrCodeJson.getInteger("ri"));
                row.setHeight((short) 360);
            } else {
                row = sheet.getRow(qrCodeJson.getInteger("ri"));
            }

            // 原图片宽高。
            double imageWidth = qrCodeJson.getInteger("width");
            double imageHeight = qrCodeJson.getInteger("height");

            // 列宽像素
            double cellWidth = sheet.getColumnWidthInPixels(qrCodeJson.getInteger("ci"));
            // getHeightInPoints()方法获取的是点（磅），就是excel设置的行高，1英寸有72磅，一般显示屏一英寸是96个像素: 行高像素
            double cellHeight = row.getHeightInPoints() / 72 * 96;

            // 图片宽 / 列宽
            double cellWidthD = imageWidth / cellWidth;
            int cellWidthI= (int) cellWidthD;

            // 图片高 / 列高
            double rowHeightD = imageHeight / cellHeight;
            int rowHeightDI = (int) rowHeightD;
            //  1023、255 为宽高  此从第r行、c列开始 、、、 扩充到 r+rowHeightDI行、c+cellWidthI列
//            XSSFClientAnchor anchor = new XSSFClientAnchor
//                    (0,0,1025,255,
//                            qrCodeJson.getInteger("ci"),
//                            row.getRowNum(),
//                            qrCodeJson.getInteger("ci")+ (cellWidthI == 0 ? 1 : cellWidthI),
//                            row.getRowNum() + (rowHeightDI == 0 ? 1 : rowHeightDI));
//            anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
//            //画图器    一个sheet只能一个
            XSSFDrawing drawing = sheet.createDrawingPatriarch();
//            drawing.createPicture(anchor , workbook.addPicture(byteArrayOut.toByteArray() , XSSFWorkbook.PICTURE_TYPE_PNG));


            CreationHelper helper = workbook.getCreationHelper();
            int jdpictureIdx = workbook.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_JPEG);// 根据需要调整参数，如果是PNG，就改为 Workbook.PICTURE_TYPE_PNG
            // 插入图片，如果原图宽度大于最终要求的图片宽度，就按比例缩小，否则展示原图
            ClientAnchor jdanchor = helper.createClientAnchor();
            // 起始画图的行列。
            jdanchor.setCol1(qrCodeJson.getInteger("ci"));
            jdanchor.setRow1(row.getRowNum());
            Picture pict = drawing.createPicture(jdanchor, jdpictureIdx);
            pict.resize();
        }
    }
}
